﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using BusinessObject;
using System.Collections;

namespace DataBase
{
    public class UserDbFunctionality
    {
        string ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Aami\\documents\\visual studio 2010\\Projects\\RestHouseReservation\\DataBase\\Reservation.mdf;Integrated Security=True;User Instance=True";
       
        public void AddUser(UserDao user)  // Creating New User
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "insert into Users(Name,Rank,PaNumber,Email,Phone,Password,Status) values('" + user.Name + "','" + user.Rank + "','" + user.PaNumber + "','" + user.Email + "','" + user.Phone + "','" + user.Password + "','" + "Pending" + "')";
                SqlCommand command = new SqlCommand(query, connection);
                int result = command.ExecuteNonQuery();
            }
        }
        public List<UserDao> PendingUsers()
        {
            List<UserDao> List = new List<UserDao>();
            
            bool flag = false;
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Users Where Status = 'Pending'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    UserDao user = new UserDao();
                    flag = true;
                    user.Name = reader["Name"].ToString();
                    user.Email = reader["Email"].ToString();
                    user.Rank = reader["Rank"].ToString();
                    user.PaNumber = reader["PaNumber"].ToString();
                    user.Phone = reader["Phone"].ToString();
                    List.Add(user);
                }
            }
            if (flag == false)
            {
                UserDao obj = new UserDao();
                List.Add(obj);
                List[0].PaNumber = "empty";
            }
            return List;
        }
        public void ChangeStatus(string PaNumber)
        {
             SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "update Users set Status = 'Active' where PaNumber = '" + PaNumber + "'";
                SqlCommand command = new SqlCommand(query, connection);
                int result = command.ExecuteNonQuery();
            }
        }
        public void DisableAccount(string PaNum)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "update Users set Status = 'Disabled' where PaNumber = '" + PaNum + "'";
                SqlCommand command = new SqlCommand(query, connection);
                int result = command.ExecuteNonQuery();
            }
        }
        public UserDao AdminLoginDb()   // Identifying The Administrator
        {
            UserDao user = new UserDao();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Users Where PaNumber = '"+"admin123"+"'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                user.Email= reader["Email"].ToString();
                user.Password=reader["Password"].ToString();
            }
            return user;
        }
        public UserDao GetUser(string PaNum)
        {
            UserDao user = new UserDao();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Users Where PaNumber = '" + PaNum + "'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                user.Name = reader["Name"].ToString();
                user.Rank = reader["Rank"].ToString();
                user.PaNumber = reader["PaNumber"].ToString();
                user.Email = reader["Email"].ToString();
                user.Phone = reader["Phone"].ToString();
            }
            return user;
        }
        public void Update(UserDao User, string PaNumber)
        {
            if (User.Email != "")
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConnectionString;
                using (con)
                {
                    con.Open();
                    string query = "update Users set Email = '" + User.Email + "' where PaNumber = '" + PaNumber + "'";
                    SqlCommand command = new SqlCommand(query, con);
                    int res = command.ExecuteNonQuery();
                }
            }
            if (User.Password != "")
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConnectionString;
                using (con)
                {
                    con.Open();
                    string query = "update Users set Password = '" + User.Password + "' where PaNumber = '" + PaNumber + "'";
                    SqlCommand command = new SqlCommand(query, con);
                    int res = command.ExecuteNonQuery();
                }

            }
            if (User.Phone != "")
            {
                SqlConnection con = new SqlConnection();
                con.ConnectionString = ConnectionString;
                using (con)
                {
                    con.Open();
                    string query = "update Users set Phone = '" + User.Phone + "' where PaNumber = '" + PaNumber + "'";
                    SqlCommand command = new SqlCommand(query, con);
                    int res = command.ExecuteNonQuery();
                }
            }
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConnectionString;
            using (conn)
            {
                conn.Open();
                string query = "update Users set Status = '" + User.Status + "' where PaNumber = '" + PaNumber + "'";
                SqlCommand command = new SqlCommand(query, conn);
                int res = command.ExecuteNonQuery();
            }
            conn.ConnectionString = ConnectionString;
            using (conn)
            {
                conn.Open();
                string query = "update Users set Rank = '" + User.Rank + "' where PaNumber = '" + PaNumber + "'";
                SqlCommand command = new SqlCommand(query, conn);
                int res = command.ExecuteNonQuery();
            } 
        }
        public bool CheckPaNumberAvalibility(string Pa)  //Checking Weather The PA Number Already Exists or not
        {
            string PaNumber = "";
            bool flag = true;
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Users";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    PaNumber = reader["PaNumber"].ToString();
                    if (PaNumber.Equals(Pa))
                    {
                        flag = false;
                        break;
                    }
                }
            }
            return flag;
        }
        public bool CheckEmailAvalibility(string Email)  //Checking Weather The Email is Already Exists or not
        {
            string DbEmail = "";
            bool flag = true;
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Users";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DbEmail = reader["PaNumber"].ToString();
                    if (DbEmail.Equals(Email))
                    {
                        flag = false;
                        break;
                    }
                }
            }
            return flag;
        }
        public UserDao UserLogin(string PaNum)  // Identifying The User
        {
            bool flag = false;
            string pa = "";
            UserDao user = new UserDao();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Users";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    pa = reader["PaNumber"].ToString();
                    if (pa.Equals(PaNum))
                    {
                        flag = true;
                        user.Email = reader["Email"].ToString();
                        user.Password = reader["Password"].ToString();
                        user.Rank = reader["Status"].ToString();
                        user.PaNumber = PaNum;
                    }
                }
            }
            if(flag == false)
            {
                user.Rank = "5";
            }
            return user;
        }
    }


    public class ReservationDbFunctionality
    {
        string ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Aami\\documents\\visual studio 2010\\Projects\\RestHouseReservation\\DataBase\\Reservation.mdf;Integrated Security=True;User Instance=True";
        public void SaveReservation(ReservationDao Reservation)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "insert into Reservation(CheckIn,CheckOut,Status,RoomType,PaNumber,Name,Rank,AssignedRoom) values('" + Reservation.CheckIn + "','" + Reservation.CheckOut + "','" + Reservation.Status + "','" + Reservation.RoomType + "','" + Reservation.PaNumber + "','" + Reservation.Name + "','" + Reservation.Rank + "','None')";
                SqlCommand command = new SqlCommand(query, connection);
                int result = command.ExecuteNonQuery();
            }
        }
        public void ConformReservation(ReservationDao Reservation)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "Update RestHouseM set CheckIn = '" + Reservation.CheckIn + "',CheckOut= '" + Reservation.CheckOut + "',Status= '" + Reservation.Status + "',ReservedFor= '" + Reservation.PaNumber + "',Name= '" + Reservation.Name + "',Rank= '" + Reservation.Rank + "' where RoomNo = '" + Reservation.RoomNo + "'";
                SqlCommand command = new SqlCommand(query, connection);
                int result = command.ExecuteNonQuery();
            }
        }
        public ReservationDao ExtractInfoFromReservation(string Request)
        {
            ReservationDao reservation = new ReservationDao();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Reservation Where RequestId = '" + Request + "'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();

                reservation.CheckIn = reader["CheckIn"].ToString();
                reservation.CheckOut = reader["CheckOut"].ToString();
                reservation.PaNumber = reader["PaNumber"].ToString();
                reservation.Name = reader["Name"].ToString();
                reservation.Rank = reader["Rank"].ToString();

            }
            return reservation;
        }
        public void UpdateStatus(string Request, string RoomNo)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "update Reservation set Status = 'Reserved' , AssignedRoom = '" + RoomNo + "' where RequestId = '" + Request + "'";
                SqlCommand command = new SqlCommand(query, connection);
                int result = command.ExecuteNonQuery();
            }
        }
        public List<ReservationDao> PendingUsers()
        {
            List<ReservationDao> List = new List<ReservationDao>();

            bool flag = false;
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Reservation Where Status = 'Pending'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    ReservationDao user = new ReservationDao();
                    flag = true;
                    user.RequestId = reader["RequestId"].ToString();
                    user.CheckIn = reader["CheckIn"].ToString();
                    user.CheckOut = reader["CheckOut"].ToString();
                    user.RoomType = reader["RoomType"].ToString();
                    user.PaNumber = reader["PaNumber"].ToString();
                    user.Name = reader["Name"].ToString();
                    user.Rank = reader["Rank"].ToString();
                    List.Add(user);
                }
            }
            if (flag == false)
            {
                ReservationDao obj = new ReservationDao();
                List.Add(obj);
                List[0].PaNumber = "empty";
            }
            return List;
        }
        public bool CheckRooms()
        {
            bool flag = false;
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select Status from RestHouseM Where Status = 'Free'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    flag = true;
                }
            }
            return flag;
        }
        public ReservationDao Obj_Cancell_Reservation(string RoomNo)
        {
            ReservationDao Reservation = new ReservationDao();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from RestHouseM Where RoomNo = '" + RoomNo + "'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Reservation.CheckIn = reader["CheckIn"].ToString();
                Reservation.CheckOut = reader["CheckOut"].ToString();
                Reservation.PaNumber = reader["ReservedFor"].ToString();
                Reservation.Name = reader["Name"].ToString();
                Reservation.Rank = reader["Rank"].ToString();
            }
            return Reservation;
        }
        public void ConfirmCancelation(string RoomNo)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "Update RestHouseM set Status = 'Free', ReservedFor = 'None' where RoomNo = '" + RoomNo + "'";
                SqlCommand command = new SqlCommand(query, connection);
                command.ExecuteNonQuery();
            }
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "Update Reservation set Status = 'Canceled', AssignedRoom = 'None' where AssignedRoom = '" + RoomNo + "'";
                SqlCommand command = new SqlCommand(query, connection);
                command.ExecuteNonQuery();
            }
        }
        public int ReservedRooms()
        {
            int result = 0;
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select Status from RestHouseM Where Status = 'Reserved'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result++;
                }
            }
            return result;
        }
        public List<ReservationDao> MyReservation(DateTime Start , DateTime End , string PaNumber)
        {
            List<ReservationDao> List = new List<ReservationDao>();
            bool flag = false;
            bool flag2 = false;
            DateTime DbStart = new DateTime();
            DateTime DbEnd = new DateTime();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            using (connection)
            {
                connection.Open();
                string query = "select * from Reservation Where PaNumber = '"+PaNumber+"'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    flag = true;
                    ReservationDao user = new ReservationDao();
                    user.CheckIn = reader["CheckIn"].ToString();
                    user.CheckOut = reader["CheckOut"].ToString();
                    DbStart = Convert.ToDateTime(user.CheckIn);
                    DbEnd = Convert.ToDateTime(user.CheckOut);
                    if (Start <= DbStart && End>=DbEnd)
                    {
                        flag2 = true;
                        user.RequestId = reader["RequestId"].ToString();
                        user.RoomNo = reader["AssignedRoom"].ToString();
                        user.RoomType = reader["RoomType"].ToString();
                        user.CheckOut = reader["CheckOut"].ToString();
                        user.Status = reader["Status"].ToString();
                        List.Add(user);
                    }
                }
            }
            if (flag == false)
            {
                ReservationDao obj = new ReservationDao();
                List.Add(obj);
                List[0].PaNumber = "empty";
            }
            if (flag2 == false)
            {
                ReservationDao obj = new ReservationDao();
                List.Add(obj);
                List[0].PaNumber = "empty";
            }
            return List;
        }
    }
}
